package com.seckill.monitor;

import com.seckill.util.TimeUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/*****
 * @Author: http://www.itheima.com
 * @Description: com.seckill.monitor.MonitorItemsAccess
 ****/
@Component
public class MonitorItemsAccess {
    
    @Value("${druidurl}")
    private String druidurl;
    @Autowired
    private RedisTemplate redisTemplate;
    
    /*****
     * 从ApacheDruid中查询指定数据
     */
    public List<String> loadData() throws Exception {
        //2.创建链接配置
        Properties connectionProperties = new Properties();
        String sql = "SELECT \n" +
                "       COUNT(\"count\") AS ViewCount,\n" +
                "       uri\n" +
                "FROM itheima\n" +
                "WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '5' HOUR\n" +
                "GROUP BY uri\n" +
                "ORDER BY ViewCount DESC\n" +
                "LIMIT 2";
        //3.获得链接对象
        Connection connection = DriverManager.getConnection(druidurl, connectionProperties);
        //4.Statment
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // preparedStatement.setInt(1, 2);
        
        //5.执行DruidSQL
        ResultSet resultSet = preparedStatement.executeQuery();
        
        //6.获取指定结果集
        List<String> ids = new ArrayList<String>();
        while (resultSet.next()) {
            String uri = resultSet.getString("uri");
            System.out.println(resultSet.getString("ViewCount"));
            // /items/ 2020 .html
            uri = uri.replaceFirst("/items/", "").replace(".html", "");
            ids.add(uri);
        }
        
        //7.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
        return ids;
    }
    
    /***
     *组装SQL(zhz:没啥作用,好像没啥用)
     *@return
     */
    public String druidSQL() {
        //加载所有热点秒杀商品的ID
        Set<String> keys = redisTemplate.keys("SKU_*");
        
        //1天前的时间
        String yesterday = TimeUtil.date2FormatYYYYMMDDHHmmss(
                TimeUtil.addDateHour(new Date(), -24));
        
        //SQL语句
        String sql = "SELECT uri,count(*) AS \"viewcount\" FROM" +
                "(SELECT * FROM \"itheima\" " +
                "WHERE        __time > '" + yesterday + "' ";
        
        //排除掉已经存在的数据
        if (keys != null && keys.size() > 0) {
            StringBuffer buffer = new StringBuffer();
            for (String key : keys) {
                buffer.append("'/items/" + key.substring(4) + ".html',");
            }
            String ids = buffer.toString().substring(0, buffer.toString().length() - 1);
            //组装SQL
            sql += " AND uri NOT IN(" + ids + ")";
        }
        //排序部分组装
        sql += " ORDER BY __time DESC) GROUP BY uri HAVING \"viewcount\">1 LIMIT 5000";
        return sql;
    }
}
